/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package kyakya.manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import kyakya.Comment;
import kyakya.Post;
import kyakya.User;
import kyakya.Util;

/**
 *
 * @author hartono
 */
public class DBManager {

    private static Connection mConn;
    private static Statement mStatement;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            mConn = DriverManager.getConnection("jdbc:mysql://localhost/progin_171_13509024", "progin", "progin");
            mStatement = mConn.createStatement();


        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static boolean insertNewPost( Post newPost) {
        if (newPost != null) {
//        Insert INTO `tbl_post` values ( NULL, '".$judul ."
//        ','".$content."', '".$tag ."' , NULL ,
//        '".$createTime ."','".$createTime."','".$userid ."',0,0,0)
            String query = "";            
            query = "Insert INTO `tbl_post` values ( NULL, '" +newPost.getTitle()
                    + "','"+newPost.getContent()
                    + "','"+newPost.getTags()
                    + "',NULL"
                    + ",'"+newPost.getCreateTime()
                    + "','"+newPost.getCreateTime()
                    + "','"+newPost.getAuthorID()
                    + "','0"
                    + "','0"
                    + "','0');";                                                    
           
            try {
                int result = mStatement.executeUpdate(query);                                 
                
                String[] tags=  newPost.getTags().split(",");
                for (String tag : tags){                    
                    String query2 = "select * from `tbl_tag` where name='"+tag+"'";
                    ResultSet set = mStatement.executeQuery(query2);
                    if (set.next()){
                        int id = set.getInt("id");
                        String query3 = "UPDATE `tbl_tag` SET `frequency` = `frequency` + 1 WHERE `id` ="+id+";"; // increment
                        mStatement.executeUpdate(query3);
                    }else{
                        String query3="insert into `tbl_tag` values(NULL, '"+tag+"', 1);";
                        mStatement.executeUpdate(query3);
                    }                    
                    set.close();    
                }
                
                
//                int result = 1;
                return result != 0;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }

        }
        return false;
    }

    public static Post getPost(int postID) throws SQLException {
        String query = "SELECT * from `tbl_post` where `id` = '" +postID +"';";
        System.out.println("query "+query);
        ResultSet results = mStatement.executeQuery(query);
        int i = 0;
        Post post = null;
        while (results.next()) {
            post = new Post();
            post.setId(results.getInt("id"));
            post.setTitle(results.getString("title"));            
            post.setAuthorID(results.getInt("author_id"));
            post.setContent(results.getString("content"));
            post.setLikeCount(results.getInt("like_count"));
            post.setDislikeCount(results.getInt("dislike_count"));
            post.setCommentCount(results.getInt("comment_count"));
            post.setUpdateTime(results.getString("update_time"));
            post.setCreateTime(results.getString("create_time"));
            post.setTags(results.getString("tags"));            
            i++;
        }
        results.close();
        System.out.println("post count " + i);
        if (post == null) {
            System.out.println("null");
        }else{
            System.out.println("post "+post);
        }
        return post;        
       
    }
    
    public static Post getPost(String title, Date createTime) throws SQLException{        
        String query = "SELECT * from `tbl_post` where `title` = '" + title + "' and `create_time` = '"+
                Util.DateToMySQLDate(createTime) +"';";
        System.out.println("Query : "+query);
        ResultSet results = mStatement.executeQuery(query);
        
        int i = 0;
        Post post = null;
        while (results.next()) {
            post = new Post();
            post.setId(results.getInt("id"));
                post.setTitle(title);            
            post.setAuthorID(results.getInt("author_id"));
            post.setContent(results.getString("content"));
            post.setLikeCount(results.getInt("like_count"));
            post.setDislikeCount(results.getInt("dislike_count"));
            post.setCommentCount(results.getInt("comment_count"));
            post.setUpdateTime(results.getString("update_time"));
            post.setCreateTime(results.getString("create_time"));
            post.setTags(results.getString("tags"));            
            i++;
        }
        results.close();
        System.out.println("post count " + i);
        if (post == null) {
            System.out.println("null");
        }else{
            System.out.println("post "+post);
        }
        return post;        
    }
    
    public static boolean editContent(int postID, String content){
        String query  = "UPDATE `tbl_post` SET `content` ='"+content+"' WHERE `id` ="+postID+";";
        int result = 0;
        try {
            result = mStatement.executeUpdate(query);
            return result!=0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }                
    }
    

    public static boolean likePost(int userID, int postID) {

        return true;
    }

    public static boolean dislikePost(int userID, int postID) {

        return true;
    }

    public static Post[] getPostsByAuthorID(int authorID) {

        return null;
    }

    // komentar 
    public static boolean insertNewComment(int userID, int postID, String comment) {

        return true;
    }

    public static boolean deleteComment(int userID, int commentID) {
        return true;
    }

    public static Comment[] getCommentsAtPost(int postID) {
        return null;
    }

    public static User getUserByID(int id) throws SQLException {
        ResultSet results = mStatement.executeQuery("select * from `tbl_user` where `id` = " + id + ";");
        int i = 0;
        User user = null;
        while (results.next()) {
            user = new User();
            user.setId(results.getInt("id"));
            user.setUsername(results.getString("username"));
            user.setAbout(results.getString("about_me"));
            user.setAvatarPath(results.getString("avatar"));
            user.setBirthday(results.getString("tanggal_lahir"));
            user.setCommentCount(results.getInt("comment_count"));
            user.setDislikeCount(results.getInt("dislike_count"));
            user.setEmail(results.getString("email"));
            user.setGender(results.getString("gender"));
            user.setLikeCount(results.getInt("like_count"));
            user.setName(results.getString("nama"));
            user.setPostCount(results.getInt("post_count"));
            user.setPassword(results.getString("password"));
            System.out.println("User :" + user);
            i++;
        }
        results.close();      
        return user;
    }

    public static User getUserByUsername(String username) throws SQLException {
        if (username != null) {
            ResultSet results = mStatement.executeQuery("select * from `tbl_user` where `username` = '" + username + "';");
            int i = 0;
            User user = null;

            while (results.next()) {
                user = new User();
                user.setId(results.getInt("id"));
                user.setUsername(results.getString("username"));
                user.setAbout(results.getString("about_me"));
                user.setAvatarPath(results.getString("avatar"));
                user.setBirthday(results.getString("tanggal_lahir"));
                user.setCommentCount(results.getInt("comment_count"));
                user.setDislikeCount(results.getInt("dislike_count"));
                user.setEmail(results.getString("email"));
                user.setGender(results.getString("gender"));
                user.setLikeCount(results.getInt("like_count"));
                user.setName(results.getString("nama"));
                user.setPostCount(results.getInt("post_count"));
                user.setPassword(results.getString("password"));
                System.out.println("User :" + user);
                i++;
            }
            results.close();           
            return user;
        }
        return null;
    }

    public static User getUserByEmail(String email) throws SQLException {
        if (email != null) {
            ResultSet results = mStatement.executeQuery("select * from `tbl_user` where `email` = '" + email + "';");
            int i = 0;
            User user = null;

            while (results.next()) {
                user = new User();
                user.setId(results.getInt("id"));
                user.setUsername(results.getString("username"));
                user.setAbout(results.getString("about_me"));
                user.setAvatarPath(results.getString("avatar"));
                user.setBirthday(results.getString("tanggal_lahir"));
                user.setCommentCount(results.getInt("comment_count"));
                user.setDislikeCount(results.getInt("dislike_count"));
                user.setEmail(results.getString("email"));
                user.setGender(results.getString("gender"));
                user.setLikeCount(results.getInt("like_count"));
                user.setName(results.getString("nama"));
                user.setPostCount(results.getInt("post_count"));
                user.setPassword(results.getString("password"));                
                i++;
            }
            results.close();
            return user;
        }
        return null;
    }

    public static boolean insertNewUser(User user) throws SQLException {
        if (user != null) {
            String query = "";
            
            query = "Insert INTO `tbl_user` values ( NULL, '" + user.getUsername()
                    + "','" + user.getPassword()
                    + "','" + user.getName()
                    + "','salt"
                    + "','" + user.getEmail()
                    + "','" + Util.DateToMySQLDate(Util.DateToDate(user.getBirthday()))
                    + "','" + user.getGender()
                    + "','" + user.getAbout()
                    + "','0"
                    + "','0"
                    + "','" + user.getAvatarPath()
                    + "','0"
                    + "','0');";
            System.out.println("Insert new user : "+query);
            try {
                int result = mStatement.executeUpdate(query);
//                int result = 1;
                return result != 0;
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }

        }
        return false;
    }

    public static boolean editEmailUser(int userID, String newEmail) {

        return true;
    }

    public static boolean editAboutUser(int userID, String about) {

        return true;
    }
    
    public static boolean incrementPostUser(int userID){
        String query = "UPDATE `tbl_user` SET `post_count` = `post_count` + 1 WHERE `id` ="+userID+";";
        
        int result=0;
        try {
            result=  mStatement.executeUpdate(query);
            return result!=0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }                
    }
    
    public static boolean incrementLikeUser(int userID){
        String query = "UPDATE `tbl_user` SET `like_count` = `like_count` + 1 WHERE `id` ="+userID+";";
        
        int result=0;
        try {
            result=  mStatement.executeUpdate(query);
            return result!=0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }                
    }
    
    public static boolean incrementDislikeUser(int userID){
        String query = "UPDATE `tbl_user` SET `dislike_count` = `dislike_count` + 1 WHERE `id` ="+userID+";";
        
        int result=0;
        try {
            result=  mStatement.executeUpdate(query);
            return result!=0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }                
    }
    
    public static boolean incrementCommentUser(int userID){
        String query = "UPDATE `tbl_user` SET `comment_count` = `comment_count` + 1 WHERE `id` ="+userID+";";
        
        int result=0;
        try {
            result=  mStatement.executeUpdate(query);
            return result!=0;
        } catch (SQLException ex) {
            Logger.getLogger(DBManager.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }                
    }
}
